# -*- coding: utf-8 -*-
# author: AZJ
# date: 2024/11/7
# 假如命运扼住了你的咽喉，你就去挠她的咯吱窝
import openpyxl
import pymysql
import random
import os

def save_xl():
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "Date"
    # id, realname, telphone,email, age, sex, sex, address, sal, job, company
    for i in range(2000):
        id = i+1
        realname = "0000" + str(id)
        telphone = random.randint(10000000000,19999999999)
        email = str(random.randint(100000000,9999999999)) + "@qq.com"
        age = random.randint(20,40)
        seed = random.randint(0, 1)
        if seed == 0:
            sex = "男"
        else:
            sex = "女"
        address = "郑州"+str(random.randint(0,30000))+"号"
        salary = random.randint(10000,999999)
        job_seed = random.randint(1,10)
        if job_seed == 1:
            job = "软件测试"
        elif job_seed == 2:
            job = "Java开发"
        elif job_seed == 3:
            job = "python开发"
        elif job_seed == 4:
            job = "网络安全工程师"
        elif job_seed == 5:
            job = "嵌入式开发"
        elif job_seed == 6:
            job = "运营"
        else:
            job = "销售"
        company = "宏软"
        data = [id, realname, telphone, email, age, sex, address, salary, job, company]
        ws.append(data)
    wb.save("hongruan.xlsx")

conn = pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    passwd='root123',
    db='test',
    charset='utf8'
)
file_path = "hongruan.xlsx"
wb = object
if os.path.exists(file_path):
    wb = openpyxl.load_workbook(file_path)
else:
    save_xl()
    wb = openpyxl.load_workbook(file_path)
ws = wb.active
data_all = []
for row in ws:
    data_list = []
    for cell in row:
        data_list.append(cell.value)
    data_all.append(tuple(data_list))
    # break
for i in data_all:
    if len(i) != 10:
        print(i)
# print(data_all[0])
# for i in data_all[0]:
#     print(type(i))
try:
    with conn.cursor() as cursor:
        sql = "INSERT INTO user_info VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
        cursor.executemany(sql, data_all)
        conn.commit()
except Exception as e:
    print("数据库操作出现错误:\n", e)
finally:
    cursor.close()
    conn.close()